import mysql.connector

from flask import Flask, render_template, request, redirect, url_for, session
from werkzeug.security import generate_password_hash,check_password_hash

app = Flask(__name__)
app.secret_key = 'CloudEasy2023'


db_config = {
    'host': "1.1.1.1",
    'user': 'root',
    'password': '********',
    'database': 'student_db',
    'charset': 'utf8mb4',
    'collation': 'utf8mb4_unicode_ci',
    'port': 3306,
    'ssl_disabled': True,
    'use_pure': True
}

# 登陆验证装饰器
def login_required(f):
    def wrap(*args, **kwargs):
        if 'logged_in' not in session:
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    wrap.__name__ = f.__name__
    return wrap


@app.route('/')
@login_required
def home():
    return render_template('index.html')

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']

        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM users WHERE username = %s",(username,))
        user = cursor.fetchone()
        cursor.close()
        conn.close()

        if user and check_password_hash(user['password_hash'], password):
            session['logged_in'] = True
            session['username'] = username
            return redirect(url_for('home'))
        return "Invalid credentials", 401
    return render_template('login.html')

@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('login'))


@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']

        password_hash = generate_password_hash(password)

        conn = mysql.connector.connect(**db_config)

        cursor = conn.cursor()

        try:
            cursor.execute("INSERT INTO users (username, password_hash) VALUES (%s, %s)",(username, password_hash))
            conn.commit()
        except mysql.connector.Error as e:
            if e.errno == 1062:
                return "Username already exists", 400
            else:
                return "Database error", 500
        finally:
            cursor.close()
            conn.close()

        return redirect(url_for('login'))
    return render_template('register.html')

@app.route('/admin')
@login_required
def admin():
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT id, username FROM users")
    users = cursor.fetchall()
    cursor.close()
    conn.close()
    return render_template('admin.html', users=users)


@app.route('/add', methods=['GET', 'POST'])
def add_student():
    if request.method == 'POST':
        student_id = request.form['id']
        name = request.form['name']
        score = request.form['score']

        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor()

        query = "INSERT INTO students (id,name,score) VALUES (%s, %s, %s)"
        values = (student_id, name, score)
        cursor.execute(query, values)
        conn.commit()
        cursor.close()
        conn.close()

        return redirect(url_for('home'))

    return render_template('add_student.html')

@app.route('/view')
def view_students():
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM students")
    students = cursor.fetchall()
    cursor.close()
    conn.close()

    return render_template('view_students.html', students=students)
if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0')